﻿using FCDB.Entity;
using FCDB.Entity.BusiReportEntity;
using FCDB.Entity.EntityEx;
using FCDB.Repository;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace FOA.Repository
{
    public class BusiReportRepository : LogicBase, ILogic
    {

        public ReportFieldSettings GetUserFieldSetting(Guid userID)
        {
            return DBHelper.ReportFieldSettings.FirstOrDefault(t => t.UserID == userID);
        }

        public IList<ReportFieldSettings>  GetUserFieldSettingList(Guid userID)
        {
            var query = from item in DBHelper.ReportFieldSettings
                        where item.IsEnable == true
                        select item;
            return query.ToList(); 

        } 
         
        public IList<FormCDBEx> GetBusiDetail( BusinessReportModel model, out int allRowsCount)
        {
         
            var query = from item in DBHelper.FormCDB
                        join proces  in DBHelper.WfProcessInstance on item.AppInstanceID equals proces.AppInstanceID
                        join Employee in DBHelper.vw_Employees on item.Salesman equals Employee.UserID
                        join pos in DBHelper.vw_EmployeeInPositions on Employee.EMPCode equals pos.EMPCode
                        join dept in DBHelper.vw_Departments on pos.DEPTCode equals dept.DEPTCode
                        join costItem in DBHelper.FormCost
                        on item.ID equals costItem.FormCDBID into cdb
                        from ur in cdb.DefaultIfEmpty()
                        where ur.FormCDBID == null &&pos.IsPrimary==true
                      
                        select new FormCDBEx
                        {
                            AchievementTime = item.AchievementTime,
                            ActualCharge = item.ActualCharge,
                            ActualRate = item.ActualRate,
                            Amount = item.Amount,
                            AppInstanceID = item.AppInstanceID,
                            ApplyAddress = item.ApplyAddress,
                            ApplyGuarantee = item.ApplyGuarantee,
                            ApplyIDCard = item.ApplyIDCard,
                            ApplyUser = item.ApplyUser,
                            ApplyUserID = item.ApplyUserID,
                            ArbitrationNumber = item.ArbitrationNumber,
                            Bond = item.Bond,
                            BusinessRemark = item.BusinessRemark,
                            financiaAmount = item.financiaAmount,
                            CaseNumber = item.CaseNumber,
                            CaseStatus = item.CaseStatus,
                            CauseOfAction = item.CauseOfAction,
                            ChargingDate = item.ChargingDate,
    
                            CommissionSend = item.CommissionSend,
                            CompanyAddress = item.CompanyAddress,
                            CompanyGuarantee = item.CompanyGuarantee,
                            CompanyTel = item.CompanyTel,
                            CompanyUser = item.CompanyUser,
                            ContractAmount = item.ContractAmount,
                            Court = item.Court,
                            CreateTime = item.CreateTime,
                            CreateUserID = item.CreateUserID,
          
                            Department = item.Department,
                            DepartmentID = item.DepartmentID,
                            FinanceRemark = item.FinanceRemark,
                            FormGuid = item.FormGuid,
                            FormTitle = item.FormTitle,
                            GeneralRemark = item.GeneralRemark,
                            ID = proces.AppInstanceID,
             
                            GuaranteeSituation = item.GuaranteeSituation,
                            IsCharge = item.IsCharge,
                            Status = item.Status,
                            InsuranceCooperation = item.InsuranceCooperation,
                        
                            pact_no = item.pact_no,
                            Salesman = item.Salesman,
                            UpdateUserID = item.UpdateUserID,
                            IsGetFile = item.IsGetFile,
                            Rate = item.Rate,
                            ProcessGuid = item.ProcessGuid,
                            UpdateTime = item.UpdateTime,
                            Respondent = item.Respondent,
                            ReceiptInfo = item.ReceiptInfo,
                            PayableAmount = item.PayableAmount,
                            Quota = item.Quota,
                            RebateTime = item.RebateTime, 
                            Judge = item.Judge,
                            Intermediary = item.Intermediary,
  
                            DataSend = item.DataSend,
                            CommissionApr=item.CommissionApr,
                             CreateUserName=Employee.CHNName,
                             DepartmentName=dept.Name,
                              DeptCode=dept.DEPTCode ,
                            SalemanName = Employee.CHNName,
                             AccountNo=item.AccountNo,
                              AccountRemark=item.AccountRemark,
                               Bank=item.Bank,
                                BZJjine=item.BZJjine,
                                 CompanyName=item.CompanyName,
                                  ContactName=item.ContactName,
                                   ContactTpey=item.ContactTpey,
                                    DBjine=item.DBjine,
                                     Due=item.Due,
                                      FCSSfeiyong=item.FCSSfeiyong,
                                       FCZbili=item.FCZbili, 
                                        FYfeiyong=item.FCSSfeiyong,
                                          GuaranteeTime=item.GuaranteeTime,
                                           GuaranteeType=item.GuaranteeType,
                                            ZBjine=item.ZBjine,
                                             PaidAmount=item.PaidAmount,
                                              ZBbili=item.ZBbili,
                                               InvoiceType=item.InvoiceType,
                                                isAgree=item.isAgree,
                                                 LBgongsi=item.LBgongsi,
                                                  JLbaozhengjin=item.JLbaozhengjin,

                                        shouju=item.shouju,
                                         PayCompany=item.PayCompany,
                                          Remark=item.Remark,
                                           SealStatus=item.SealStatus,
                                            XYfeiyong=item.XYfeiyong,
                                             Taxation=item.Taxation, 
                                              TSXYfeiyong=item.TSXYfeiyong,
                                               suifei=item.suifei,
                                                TSDBjine=item.TSDBjine,
                                                 TSSHfeiyong=item.TSSHfeiyong,
                                                  PropertyClues=item.PropertyClues,
                                                   HeadID=item.HeadID,
                                                    RoleBID=item.RoleBID,
                                                     HeadApr=item.HeadApr,
                                                      RoleAApr=item.RoleAApr,
                                                       RoleAID=item.RoleAID,
                                                        RoleBApr=item.RoleBApr

                                      
                        };

            if (model.dtStart.HasValue && model.dtEnd.HasValue)
            {
                
                query = query.Where(t => t.CreateTime >= model.dtStart && t.CreateTime < model.dtEnd);
                
            }  
            if (DBHelper.UserBusinessPermissions.Count(t => t.UserID ==model.UserID)==0)
            {
                query = query.Where(t => t.Salesman == model.UserID);
            }else
            {
                var mquery=from ubp in DBHelper.UserBusinessPermissions
                           where ubp.UserID == model.UserID
                           select ubp.DEPTCode;
                IList<String> strDeptCode = mquery.ToList();
                query = query.Where(t => strDeptCode.Contains(t.DeptCode));
            }
            if (!String.IsNullOrEmpty(model.Court))
            {
                query = query.Where(t => t.Court.Contains(model.Court));
            }
            if(!String.IsNullOrEmpty(model.SalesmanID))
            {
                Guid SalesmanID=Guid.Parse(model.SalesmanID);
                query = query.Where(t => t.Salesman == SalesmanID);
            }
            if (!String.IsNullOrEmpty(model.Respondent))
            {

                query = query.Where(t => t.Respondent.Contains(model.Respondent));
            }
            if (!String.IsNullOrEmpty(model.DeptCode))
            {

                query = query.Where(t => t.DeptCode == model.DeptCode);
            }

            if (model.ChargeStatus.HasValue && model.ChargeStatus.Value>0)
            {
                if (model.ChargeStatus.Value == 1)
                {
                    query = query.Where(t => t.ChargingDate == null);
                }
                else
                {
                    query = query.Where(t => t.ChargingDate != null);
                } 
            }
            if (model.ProcessStatus.HasValue && model.ProcessStatus.Value > 0)
            { 

                if (model.ProcessStatus.Value == 1)
                {
                    query = from item in query
                            join process in DBHelper.WfProcessInstance on item.AppInstanceID equals process.AppInstanceID
                            where process.ProcessState <4
                            select item; 
                }
                else
                {
                    query = from item in query
                            join process in DBHelper.WfProcessInstance on item.AppInstanceID equals process.AppInstanceID
                            where process.ProcessState>=4
                            select item;
                } 
            }

            if (model.DebutStatus.HasValue && model.DebutStatus.Value > 0)
            {
                if (model.DebutStatus.Value == 1)
                {
                    query = query.Where(t => t.CommissionSend != true);
                   
                }
                else
                {
                    query = query.Where(t => t.CommissionSend == true);
                } 
            }

            allRowsCount = query.Count();
            int skip = (model.pageIndex.Value - 1) * model.pageSize.Value;
            IList<FormCDBEx> list = query.OrderByDescending(t => t.CreateTime).Skip(skip).Take(model.pageSize.Value).ToList();
            return list;
        }
          
        public IList<BusinessDeductReportModel> getBusinessDeductReport(BusinessReportModel model, out int allRowsCount)
        {
            var query = from item in DBHelper.FormCDB
                        join proces in DBHelper.WfProcessInstance on item.AppInstanceID equals proces.AppInstanceID
                        join Employee in DBHelper.vw_Employees on item.Salesman equals Employee.UserID
                        join pos in DBHelper.vw_EmployeeInPositions on Employee.EMPCode equals pos.EMPCode
                        join dept in DBHelper.vw_Departments on pos.DEPTCode equals dept.DEPTCode
                        join costItem in DBHelper.FormCost
                        on item.ID equals costItem.FormCDBID into cdb
                        from ur in cdb.DefaultIfEmpty()
                        where ur.FormCDBID == null && pos.IsPrimary == true
                        select new FormCDBEx
                        {

                            AchievementTime = item.AchievementTime,
                            ActualCharge = item.ActualCharge,
                            ActualRate = item.ActualRate,
                            Amount = item.Amount,
                            AppInstanceID = item.AppInstanceID,
                            ApplyAddress = item.ApplyAddress,
                            ApplyGuarantee = item.ApplyGuarantee,
                            ApplyIDCard = item.ApplyIDCard,
                            ApplyUser = item.ApplyUser,
                            ApplyUserID = item.ApplyUserID,
                            ArbitrationNumber = item.ArbitrationNumber,
                            Bond = item.Bond,
                            BusinessRemark = item.BusinessRemark,
                            financiaAmount = item.financiaAmount,
                            CaseNumber = item.CaseNumber,
                            CaseStatus = item.CaseStatus,
                            CauseOfAction = item.CauseOfAction,
                            ChargingDate = item.ChargingDate,
 
                            CommissionSend = item.CommissionSend,
                            CompanyAddress = item.CompanyAddress,
                            CompanyGuarantee = item.CompanyGuarantee,
                            CompanyTel = item.CompanyTel,
                            CompanyUser = item.CompanyUser,
                            ContractAmount = item.ContractAmount,
                            Court = item.Court,
                            CreateTime = item.CreateTime,
                            CreateUserID = item.CreateUserID,
        
                            Department = item.Department,
                            DepartmentID = item.DepartmentID,
                            FinanceRemark = item.FinanceRemark,
                            FormGuid = item.FormGuid,
                            FormTitle = item.FormTitle,
                            GeneralRemark = item.GeneralRemark,
                            ID = proces.AppInstanceID,
             
                            GuaranteeSituation = item.GuaranteeSituation,
                            IsCharge = item.IsCharge,
                            Status = item.Status,
                            InsuranceCooperation = item.InsuranceCooperation, 
                            pact_no = item.pact_no,
                            Salesman = item.Salesman,
                            UpdateUserID = item.UpdateUserID,
                            IsGetFile = item.IsGetFile,
                            Rate = item.Rate,
                            ProcessGuid = item.ProcessGuid,
                            UpdateTime = item.UpdateTime,
                            Respondent = item.Respondent,
                            ReceiptInfo = item.ReceiptInfo,
                            PayableAmount = item.PayableAmount,
                            Quota = item.Quota,
                            RebateTime = item.RebateTime,
                            Judge = item.Judge,
                            Intermediary = item.Intermediary, 
                            DataSend = item.DataSend,
                            CommissionApr = item.CommissionApr,
                            CreateUserName = Employee.CHNName,
                            DepartmentName = dept.Name,
                            DeptCode = dept.DEPTCode,
                             SalemanName=Employee.CHNName
                        }; 
            if (model.dtStart.HasValue && model.dtEnd.HasValue)
            {
                query = query.Where(t => t.CreateTime >= model.dtStart && t.CreateTime < model.dtEnd); 
            }
            if (DBHelper.UserBusinessPermissions.Count(t => t.UserID == model.UserID) == 0)
            {
                query = query.Where(t => t.Salesman == model.UserID);
            }
            else
            {
                var mquery = from ubp in DBHelper.UserBusinessPermissions
                             where ubp.UserID == model.UserID
                             select ubp.DEPTCode;
                IList<String> strDeptCode = mquery.ToList();
                query = query.Where(t => strDeptCode.Contains(t.DeptCode));
            }
            if (!String.IsNullOrEmpty(model.Court))
            {
                query = query.Where(t => t.Court.Contains(model.Court));
            }
            if (!String.IsNullOrEmpty(model.SalesmanID))
            {
                Guid SalesmanID = Guid.Parse(model.SalesmanID);
                query = query.Where(t => t.Salesman == SalesmanID);
            }
            if (!String.IsNullOrEmpty(model.Respondent))
            {

                query = query.Where(t => t.Respondent.Contains(model.Respondent));
            }
            if (!String.IsNullOrEmpty(model.DeptCode))
            {

                query = query.Where(t => t.DeptCode == model.DeptCode);
            }

            if (model.ChargeStatus.HasValue && model.ChargeStatus.Value > 0)
            {
                if (model.ChargeStatus.Value == 1)
                {
                    query = query.Where(t => t.ChargingDate == null);
                }
                else
                {
                    query = query.Where(t => t.ChargingDate != null);
                }
            }
            if (model.ProcessStatus.HasValue && model.ProcessStatus.Value > 0)
            {

                if (model.ProcessStatus.Value == 1)
                {
                    query = from item in query
                            join process in DBHelper.WfProcessInstance on item.AppInstanceID equals process.AppInstanceID
                            where process.ProcessState < 4
                            select item;
                }
                else
                {
                    query = from item in query
                            join process in DBHelper.WfProcessInstance on item.AppInstanceID equals process.AppInstanceID
                            where process.ProcessState >= 4
                            select item;
                }
            }

            if (model.DebutStatus.HasValue && model.DebutStatus.Value > 0)
            {
                if (model.DebutStatus.Value == 1)
                {
                    query = query.Where(t => t.CommissionSend != true);
                    
                }
                else
                {
                    query = query.Where(t => t.CommissionSend == true);
                }
            }
            var gquery = from item in query
                         group item by item.Salesman into bquery
                         select new BusinessDeductReportModel()
                         {
                             UserID = bquery.Key,
                             BusiCount = bquery.Count(),
                             BusiAmount = bquery.Sum(t => t.Amount),
                             ActualReciAmount = bquery.Sum(t => t.ActualCharge),
                             DeductSumAmount = bquery.Sum(t => t.CommissionApr)
                         };
            allRowsCount = gquery.Count();
            int skip = (model.pageIndex.Value - 1) * model.pageSize.Value;
            IList<BusinessDeductReportModel> list = gquery.OrderByDescending(t => t.BusiCount).Skip(skip).Take(model.pageSize.Value).ToList();
              var stsQuery = from item in list
                           join Employee in DBHelper.vw_Employees on item.UserID equals Employee.UserID
                           join pos in DBHelper.vw_EmployeeInPositions on Employee.EMPCode equals pos.EMPCode
                           join dept in DBHelper.vw_Departments on pos.DEPTCode equals dept.DEPTCode
                           select new BusinessDeductReportModel()
                           {
                               ActualReciAmount = item.ActualReciAmount,
                               UserID = item.UserID,
                               DeductSumAmount = item.DeductSumAmount,
                               BusiAmount = item.BusiAmount,
                               BusiCount = item.BusiCount,
                               DeptName = dept.Name,
                               UserName = Employee.CHNName
                           };

              return stsQuery.ToList();
        }
       
         
    }
}
